{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对数据的操作都是创建新数据，而不是在原有数据上操作"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 导入库、数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np  # 一般都会用到 numpy 库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df = pd.read_csv('query.csv', header=0)  # header 可以指定标题行\n",
    "# df = pd.read_excel('query.csv')\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"id\": [1001, 1002, 1003, 1004, 1005, 1006], \n",
    "        \"date\": pd.date_range('20130102', periods=6),\n",
    "        \"city\": ['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', 'BEIJING '],\n",
    "        \"age\": [23, 44, 54, 32, 34, 32],\n",
    "        \"category\":['100-A', '100-B', '110-A', '110-C', '210-A', '130-F'],\n",
    "        \"price\":[1200, np.nan, 2133, 5433, np.nan, 4432]\n",
    "    },\n",
    "    columns = ['id', 'date', 'city', 'category', 'age', 'price'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>SH</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date         city category  age   price\n",
       "0  1001 2013-01-02     Beijing     100-A   23  1200.0\n",
       "1  1002 2013-01-03           SH    100-B   44     NaN\n",
       "2  1003 2013-01-04   guangzhou     110-A   54  2133.0\n",
       "3  1004 2013-01-05     Shenzhen    110-C   32  5433.0\n",
       "4  1005 2013-01-06     shanghai    210-A   34     NaN\n",
       "5  1006 2013-01-07     BEIJING     130-F   32  4432.0"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 查看基本信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6, 6)"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape  # 维度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6 entries, 0 to 5\n",
      "Data columns (total 6 columns):\n",
      "id          6 non-null int64\n",
      "date        6 non-null datetime64[ns]\n",
      "city        6 non-null object\n",
      "category    6 non-null object\n",
      "age         6 non-null int64\n",
      "price       4 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(1), int64(2), object(2)\n",
      "memory usage: 368.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()  # 数据表基本信息（维度、列名称、数据格式、所占空间等）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                   int64\n",
       "date        datetime64[ns]\n",
       "city                object\n",
       "category            object\n",
       "age                  int64\n",
       "price              float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes  # 每一列数据的格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['city'].dtype  # 某一列格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id   date   city  category    age  price\n",
       "0  False  False  False     False  False  False\n",
       "1  False  False  False     False  False   True\n",
       "2  False  False  False     False  False  False\n",
       "3  False  False  False     False  False  False\n",
       "4  False  False  False     False  False   True\n",
       "5  False  False  False     False  False  False"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull()  # 是否是空值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1     True\n",
       "2    False\n",
       "3    False\n",
       "4     True\n",
       "5    False\n",
       "Name: price, dtype: bool"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price'].isnull()  # 具体某一列是否是空值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['100-A', '100-B', '110-A', '110-C', '210-A', '130-F'], dtype=object)"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['category'].unique()  # 某一列的唯一值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[1001, Timestamp('2013-01-02 00:00:00'), 'Beijing ', '100-A', 23,\n",
       "        1200.0],\n",
       "       [1002, Timestamp('2013-01-03 00:00:00'), 'SH', '100-B', 44, nan],\n",
       "       [1003, Timestamp('2013-01-04 00:00:00'), ' guangzhou ', '110-A',\n",
       "        54, 2133.0],\n",
       "       [1004, Timestamp('2013-01-05 00:00:00'), 'Shenzhen', '110-C', 32,\n",
       "        5433.0],\n",
       "       [1005, Timestamp('2013-01-06 00:00:00'), 'shanghai', '210-A', 34,\n",
       "        nan],\n",
       "       [1006, Timestamp('2013-01-07 00:00:00'), 'BEIJING ', '130-F', 32,\n",
       "        4432.0]], dtype=object)"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.values  # 整个数据表的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'date', 'city', 'category', 'age', 'price'], dtype='object')"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns  # 所有列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>SH</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date         city category  age   price\n",
       "0  1001 2013-01-02     Beijing     100-A   23  1200.0\n",
       "1  1002 2013-01-03           SH    100-B   44     NaN\n",
       "2  1003 2013-01-04   guangzhou     110-A   54  2133.0"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(3)  # 前 3 行数据，默认 n=5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date      city category  age   price\n",
       "3  1004 2013-01-05  Shenzhen    110-C   32  5433.0\n",
       "4  1005 2013-01-06  shanghai    210-A   34     NaN\n",
       "5  1006 2013-01-07  BEIJING     130-F   32  4432.0"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(n=3)  # 后10行数据，默认 n=5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. 数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>SH</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>BEIJING</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date         city category  age   price\n",
       "0  1001 2013-01-02     Beijing     100-A   23  1200.0\n",
       "1  1002 2013-01-03           SH    100-B   44    80.0\n",
       "2  1003 2013-01-04   guangzhou     110-A   54  2133.0\n",
       "3  1004 2013-01-05     Shenzhen    110-C   32  5433.0\n",
       "4  1005 2013-01-06     shanghai    210-A   34    80.0\n",
       "5  1006 2013-01-07     BEIJING     130-F   32  4432.0"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.fillna(value=80)  # 用 80 填充空值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1200.0\n",
       "1    3299.5\n",
       "2    2133.0\n",
       "3    5433.0\n",
       "4    3299.5\n",
       "5    4432.0\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price'] = df['price'].fillna(df['price'].mean())  # 使用列 price 的均值对NA进行填充\n",
    "df['price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      Beijing\n",
       "1           SH\n",
       "2    guangzhou\n",
       "3     Shenzhen\n",
       "4     shanghai\n",
       "5      BEIJING\n",
       "Name: city, dtype: object"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['city'] = df['city'].map(str.strip)  # 去除city字段的字符空格\n",
    "df['city']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      beijing\n",
       "1           sh\n",
       "2    guangzhou\n",
       "3     shenzhen\n",
       "4     shanghai\n",
       "5      beijing\n",
       "Name: city, dtype: object"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['city'] = df['city'].str.lower()  # 大小写转换\n",
    "df['city']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int64')"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price'] = df['price'].astype('int')  # 更改数据格式\n",
    "df['price'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'date', 'city', 'category-size', 'age', 'price'], dtype='object')"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.rename(columns={'category': 'category-size'}) # 更改列名\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1           sh\n",
       "2    guangzhou\n",
       "3     shenzhen\n",
       "4     shanghai\n",
       "5      beijing\n",
       "Name: city, dtype: object"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['city'].drop_duplicates(keep='last')  # 删除先出现的重复值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      beijing\n",
       "1     shanghai\n",
       "2    guangzhou\n",
       "3     shenzhen\n",
       "4     shanghai\n",
       "5      beijing\n",
       "Name: city, dtype: object"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['city'].replace('sh', 'shanghai')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. 数据预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1008</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id  gender pay  m-point\n",
       "0  1001    male   Y       10\n",
       "1  1002  female   N       12\n",
       "2  1003    male   Y       20\n",
       "3  1004  female   Y       40\n",
       "4  1005    male   N       40\n",
       "5  1006  female   Y       40\n",
       "6  1007    male   N       30\n",
       "7  1008  female   Y       20"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({\n",
    "    \"id\": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008], \n",
    "    \"gender\": ['male', 'female', 'male', 'female', 'male', 'female', 'male', 'female'],\n",
    "    \"pay\": ['Y', 'N', 'Y', 'Y', 'N', 'Y', 'N', 'Y'],\n",
    "    \"m-point\": [10, 12, 20, 40, 40, 40,  30,20]\n",
    "})\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category-size  age  price  gender pay  m-point\n",
       "0  1001 2013-01-02    beijing         100-A   23   1200    male   Y       10\n",
       "1  1002 2013-01-03         sh         100-B   44   3299  female   N       12\n",
       "2  1003 2013-01-04  guangzhou         110-A   54   2133    male   Y       20\n",
       "3  1004 2013-01-05   shenzhen         110-C   32   5433  female   Y       40\n",
       "4  1005 2013-01-06   shanghai         210-A   34   3299    male   N       40\n",
       "5  1006 2013-01-07    beijing         130-F   32   4432  female   Y       40"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner = pd.merge(df, df1, how='inner')  # 交集\n",
    "df_inner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category-size  age  price  gender pay  m-point\n",
       "0  1001 2013-01-02    beijing         100-A   23   1200    male   Y       10\n",
       "1  1002 2013-01-03         sh         100-B   44   3299  female   N       12\n",
       "2  1003 2013-01-04  guangzhou         110-A   54   2133    male   Y       20\n",
       "3  1004 2013-01-05   shenzhen         110-C   32   5433  female   Y       40\n",
       "4  1005 2013-01-06   shanghai         210-A   34   3299    male   N       40\n",
       "5  1006 2013-01-07    beijing         130-F   32   4432  female   Y       40"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_left = pd.merge(df, df1, how='left')  # 左连接\n",
    "df_left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44.0</td>\n",
       "      <td>3299.0</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54.0</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32.0</td>\n",
       "      <td>5433.0</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3299.0</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32.0</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1008</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category-size   age   price  gender pay  m-point\n",
       "0  1001 2013-01-02    beijing         100-A  23.0  1200.0    male   Y       10\n",
       "1  1002 2013-01-03         sh         100-B  44.0  3299.0  female   N       12\n",
       "2  1003 2013-01-04  guangzhou         110-A  54.0  2133.0    male   Y       20\n",
       "3  1004 2013-01-05   shenzhen         110-C  32.0  5433.0  female   Y       40\n",
       "4  1005 2013-01-06   shanghai         210-A  34.0  3299.0    male   N       40\n",
       "5  1006 2013-01-07    beijing         130-F  32.0  4432.0  female   Y       40\n",
       "6  1007        NaT        NaN           NaN   NaN     NaN    male   N       30\n",
       "7  1008        NaT        NaN           NaN   NaN     NaN  female   Y       20"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_right = pd.merge(df, df1, how='right')  # 右连接\n",
    "df_right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44.0</td>\n",
       "      <td>3299.0</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54.0</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32.0</td>\n",
       "      <td>5433.0</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3299.0</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32.0</td>\n",
       "      <td>4432.0</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1008</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category-size   age   price  gender pay  m-point\n",
       "0  1001 2013-01-02    beijing         100-A  23.0  1200.0    male   Y       10\n",
       "1  1002 2013-01-03         sh         100-B  44.0  3299.0  female   N       12\n",
       "2  1003 2013-01-04  guangzhou         110-A  54.0  2133.0    male   Y       20\n",
       "3  1004 2013-01-05   shenzhen         110-C  32.0  5433.0  female   Y       40\n",
       "4  1005 2013-01-06   shanghai         210-A  34.0  3299.0    male   N       40\n",
       "5  1006 2013-01-07    beijing         130-F  32.0  4432.0  female   Y       40\n",
       "6  1007        NaT        NaN           NaN   NaN     NaN    male   N       30\n",
       "7  1008        NaT        NaN           NaN   NaN     NaN  female   Y       20"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_outer = pd.merge(df, df1, how='outer')  # 外链接\n",
    "df_outer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1001</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002</th>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1003</th>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1004</th>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1005</th>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1006</th>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           date       city category-size  age  price  gender pay  m-point\n",
       "id                                                                       \n",
       "1001 2013-01-02    beijing         100-A   23   1200    male   Y       10\n",
       "1002 2013-01-03         sh         100-B   44   3299  female   N       12\n",
       "1003 2013-01-04  guangzhou         110-A   54   2133    male   Y       20\n",
       "1004 2013-01-05   shenzhen         110-C   32   5433  female   Y       40\n",
       "1005 2013-01-06   shanghai         210-A   34   3299    male   N       40\n",
       "1006 2013-01-07    beijing         130-F   32   4432  female   Y       40"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.set_index('id')  # 设置索引列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category-size  age  price  gender pay  m-point\n",
       "0  1001 2013-01-02    beijing         100-A   23   1200    male   Y       10\n",
       "3  1004 2013-01-05   shenzhen         110-C   32   5433  female   Y       40\n",
       "5  1006 2013-01-07    beijing         130-F   32   4432  female   Y       40\n",
       "4  1005 2013-01-06   shanghai         210-A   34   3299    male   N       40\n",
       "1  1002 2013-01-03         sh         100-B   44   3299  female   N       12\n",
       "2  1003 2013-01-04  guangzhou         110-A   54   2133    male   Y       20"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.sort_values(by=['age'])  # 按照特定列的值排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category-size  age  price  gender pay  m-point  \\\n",
       "0  1001 2013-01-02    beijing         100-A   23   1200    male   Y       10   \n",
       "1  1002 2013-01-03         sh         100-B   44   3299  female   N       12   \n",
       "2  1003 2013-01-04  guangzhou         110-A   54   2133    male   Y       20   \n",
       "3  1004 2013-01-05   shenzhen         110-C   32   5433  female   Y       40   \n",
       "4  1005 2013-01-06   shanghai         210-A   34   3299    male   N       40   \n",
       "5  1006 2013-01-07    beijing         130-F   32   4432  female   Y       40   \n",
       "\n",
       "  group  \n",
       "0   low  \n",
       "1  high  \n",
       "2   low  \n",
       "3  high  \n",
       "4  high  \n",
       "5  high  "
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 如果prince列的值>3000，group列显示high，否则显示low\n",
    "df_inner['group'] = np.where(df_inner['price'] > 3000, 'high', 'low')\n",
    "df_inner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category-size  age  price  gender pay  m-point  \\\n",
       "0  1001 2013-01-02    beijing         100-A   23   1200    male   Y       10   \n",
       "1  1002 2013-01-03         sh         100-B   44   3299  female   N       12   \n",
       "2  1003 2013-01-04  guangzhou         110-A   54   2133    male   Y       20   \n",
       "3  1004 2013-01-05   shenzhen         110-C   32   5433  female   Y       40   \n",
       "4  1005 2013-01-06   shanghai         210-A   34   3299    male   N       40   \n",
       "5  1006 2013-01-07    beijing         130-F   32   4432  female   Y       40   \n",
       "\n",
       "  group  sign  \n",
       "0   low   NaN  \n",
       "1  high   NaN  \n",
       "2   low   NaN  \n",
       "3  high   NaN  \n",
       "4  high   NaN  \n",
       "5  high   1.0  "
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对复合多个条件的数据进行分组标记\n",
    "df_inner.loc[(df_inner['city'] == 'beijing') & (df_inner['price'] >= 4000), 'sign'] = 1\n",
    "df_inner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  category size\n",
       "0      100    A\n",
       "1      100    B\n",
       "2      110    A\n",
       "3      110    C\n",
       "4      210    A\n",
       "5      130    F"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对 category-size 字段的值依次进行分列，并创建数据表，索引值为 df_inner 的索引列，列名称为 category 和 size\n",
    "pd.DataFrame((x.split('-') for x in df_inner['category-size']), index=df_inner.index, columns=['category', 'size'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category-size  age  price  gender pay  m-point  \\\n",
       "0  1001 2013-01-02    beijing         100-A   23   1200    male   Y       10   \n",
       "1  1002 2013-01-03         sh         100-B   44   3299  female   N       12   \n",
       "2  1003 2013-01-04  guangzhou         110-A   54   2133    male   Y       20   \n",
       "3  1004 2013-01-05   shenzhen         110-C   32   5433  female   Y       40   \n",
       "4  1005 2013-01-06   shanghai         210-A   34   3299    male   N       40   \n",
       "5  1006 2013-01-07    beijing         130-F   32   4432  female   Y       40   \n",
       "\n",
       "  group  sign category size  \n",
       "0   low   NaN      100    A  \n",
       "1  high   NaN      100    B  \n",
       "2   low   NaN      110    A  \n",
       "3  high   NaN      110    C  \n",
       "4  high   NaN      210    A  \n",
       "5  high   1.0      130    F  "
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "split = _\n",
    "# 将完成分裂后的数据表和原df_inner数据表进行匹配\n",
    "df_inner=pd.merge(df_inner,split,right_index=True, left_index=True)\n",
    "df_inner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. 数据提取"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "loc 按标签取，iloc 按位置取，ix 同时按标签和位置取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                              1004\n",
       "date             2013-01-05 00:00:00\n",
       "city                        shenzhen\n",
       "category-size                  110-C\n",
       "age                               32\n",
       "price                           5433\n",
       "gender                        female\n",
       "pay                                Y\n",
       "m-point                           40\n",
       "group                           high\n",
       "sign                             NaN\n",
       "category                         110\n",
       "size                               C\n",
       "Name: 3, dtype: object"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.loc[3]  # 行号为3的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id       date       city category-size  age  price  gender pay  m-point  \\\n",
       "0  1001 2013-01-02    beijing         100-A   23   1200    male   Y       10   \n",
       "1  1002 2013-01-03         sh         100-B   44   3299  female   N       12   \n",
       "2  1003 2013-01-04  guangzhou         110-A   54   2133    male   Y       20   \n",
       "3  1004 2013-01-05   shenzhen         110-C   32   5433  female   Y       40   \n",
       "4  1005 2013-01-06   shanghai         210-A   34   3299    male   N       40   \n",
       "\n",
       "  group  sign category size  \n",
       "0   low   NaN      100    A  \n",
       "1  high   NaN      100    B  \n",
       "2   low   NaN      110    A  \n",
       "3  high   NaN      110    C  \n",
       "4  high   NaN      210    A  "
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.iloc[0:5]  # 前四条数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1001</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1002</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1003</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1004</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1005</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1006</td>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index    id       date       city category-size  age  price  gender pay  \\\n",
       "0      0  1001 2013-01-02    beijing         100-A   23   1200    male   Y   \n",
       "1      1  1002 2013-01-03         sh         100-B   44   3299  female   N   \n",
       "2      2  1003 2013-01-04  guangzhou         110-A   54   2133    male   Y   \n",
       "3      3  1004 2013-01-05   shenzhen         110-C   32   5433  female   Y   \n",
       "4      4  1005 2013-01-06   shanghai         210-A   34   3299    male   N   \n",
       "5      5  1006 2013-01-07    beijing         130-F   32   4432  female   Y   \n",
       "\n",
       "   m-point group  sign category size  \n",
       "0       10   low   NaN      100    A  \n",
       "1       12  high   NaN      100    B  \n",
       "2       20   low   NaN      110    A  \n",
       "3       40  high   NaN      110    C  \n",
       "4       40  high   NaN      210    A  \n",
       "5       40  high   1.0      130    F  "
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.reset_index()  # 重设索引，增加了一个index索引列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>1004</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>1005</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>1006</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city category-size  age  price  gender pay  m-point  \\\n",
       "date                                                                         \n",
       "2013-01-02  1001    beijing         100-A   23   1200    male   Y       10   \n",
       "2013-01-03  1002         sh         100-B   44   3299  female   N       12   \n",
       "2013-01-04  1003  guangzhou         110-A   54   2133    male   Y       20   \n",
       "2013-01-05  1004   shenzhen         110-C   32   5433  female   Y       40   \n",
       "2013-01-06  1005   shanghai         210-A   34   3299    male   N       40   \n",
       "2013-01-07  1006    beijing         130-F   32   4432  female   Y       40   \n",
       "\n",
       "           group  sign category size  \n",
       "date                                  \n",
       "2013-01-02   low   NaN      100    A  \n",
       "2013-01-03  high   NaN      100    B  \n",
       "2013-01-04   low   NaN      110    A  \n",
       "2013-01-05  high   NaN      110    C  \n",
       "2013-01-06  high   NaN      210    A  \n",
       "2013-01-07  high   1.0      130    F  "
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner = df_inner.set_index('date')  # 设置日期列为索引列\n",
    "df_inner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "      <td>3299</td>\n",
       "      <td>female</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>1004</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city category-size  age  price  gender pay  m-point  \\\n",
       "date                                                                         \n",
       "2013-01-02  1001    beijing         100-A   23   1200    male   Y       10   \n",
       "2013-01-03  1002         sh         100-B   44   3299  female   N       12   \n",
       "2013-01-04  1003  guangzhou         110-A   54   2133    male   Y       20   \n",
       "2013-01-05  1004   shenzhen         110-C   32   5433  female   Y       40   \n",
       "\n",
       "           group  sign category size  \n",
       "date                                  \n",
       "2013-01-02   low   NaN      100    A  \n",
       "2013-01-03  high   NaN      100    B  \n",
       "2013-01-04   low   NaN      110    A  \n",
       "2013-01-05  high   NaN      110    C  "
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner[:'2013-01-05']  # 5号之前的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>beijing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>sh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>guangzhou</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city\n",
       "date                       \n",
       "2013-01-02  1001    beijing\n",
       "2013-01-03  1002         sh\n",
       "2013-01-04  1003  guangzhou"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.iloc[:3, :2]  # 前三条数据，前两列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            price  gender\n",
       "date                     \n",
       "2013-01-02   1200    male\n",
       "2013-01-04   2133    male\n",
       "2013-01-07   4432  female"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.iloc[[0, 2, 5], [4, 5]]  # 0、2、5行，4、5列数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/ikeliu/appli/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:1: DeprecationWarning: \n",
      ".ix is deprecated. Please use\n",
      ".loc for label based indexing or\n",
      ".iloc for positional indexing\n",
      "\n",
      "See the documentation here:\n",
      "http://pandas.pydata.org/pandas-docs/stable/indexing.html#ix-indexer-is-deprecated\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>sh</td>\n",
       "      <td>100-B</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city category-size  age\n",
       "date                                          \n",
       "2013-01-02  1001    beijing         100-A   23\n",
       "2013-01-03  1002         sh         100-B   44\n",
       "2013-01-04  1003  guangzhou         110-A   54"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.ix[:'2013-01-04', :4]  # 前2行，前四列数据  ix 不建议使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2013-01-02     True\n",
       "2013-01-03    False\n",
       "2013-01-04    False\n",
       "2013-01-05    False\n",
       "2013-01-06    False\n",
       "2013-01-07     True\n",
       "Name: city, dtype: bool"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner['city'].isin(['beijing'])  # 判断包含"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>1005</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>1006</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id      city category-size  age  price  gender pay  m-point  \\\n",
       "date                                                                        \n",
       "2013-01-02  1001   beijing         100-A   23   1200    male   Y       10   \n",
       "2013-01-06  1005  shanghai         210-A   34   3299    male   N       40   \n",
       "2013-01-07  1006   beijing         130-F   32   4432  female   Y       40   \n",
       "\n",
       "           group  sign category size  \n",
       "date                                  \n",
       "2013-01-02   low   NaN      100    A  \n",
       "2013-01-06  high   NaN      210    A  \n",
       "2013-01-07  high   1.0      130    F  "
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.loc[df_inner['city'].isin(['beijing','shanghai'])]  # 展示在北京上海中的行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           category\n",
       "date               \n",
       "2013-01-02      100\n",
       "2013-01-03      100\n",
       "2013-01-04      110\n",
       "2013-01-05      110\n",
       "2013-01-06      210\n",
       "2013-01-07      130"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(df_inner['category'].str[:3])  # 提取category列的前三个字符组成一个数据表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. 数据筛选"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>age</th>\n",
       "      <th>category</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>1006</td>\n",
       "      <td>beijing</td>\n",
       "      <td>32</td>\n",
       "      <td>130</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id     city  age category  gender\n",
       "date                                           \n",
       "2013-01-07  1006  beijing   32      130  female"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 与\n",
    "df_inner.loc[(df_inner['age'] > 25) & (df_inner['city'] == 'beijing'), ['id','city','age','category','gender']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>age</th>\n",
       "      <th>category</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>beijing</td>\n",
       "      <td>23</td>\n",
       "      <td>100</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>1004</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>32</td>\n",
       "      <td>110</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>1006</td>\n",
       "      <td>beijing</td>\n",
       "      <td>32</td>\n",
       "      <td>130</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>1005</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>34</td>\n",
       "      <td>210</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>sh</td>\n",
       "      <td>44</td>\n",
       "      <td>100</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>54</td>\n",
       "      <td>110</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city  age category  gender\n",
       "date                                             \n",
       "2013-01-02  1001    beijing   23      100    male\n",
       "2013-01-05  1004   shenzhen   32      110  female\n",
       "2013-01-07  1006    beijing   32      130  female\n",
       "2013-01-06  1005   shanghai   34      210    male\n",
       "2013-01-03  1002         sh   44      100  female\n",
       "2013-01-04  1003  guangzhou   54      110    male"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 或\n",
    "df_inner.loc[(df_inner['age'] > 25) | (df_inner['city'] == 'beijing'), ['id','city','age','category','gender']].sort_values(['age']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>age</th>\n",
       "      <th>category</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>1002</td>\n",
       "      <td>sh</td>\n",
       "      <td>44</td>\n",
       "      <td>100</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>54</td>\n",
       "      <td>110</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>1004</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>32</td>\n",
       "      <td>110</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>1005</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>34</td>\n",
       "      <td>210</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city  age category  gender\n",
       "date                                             \n",
       "2013-01-03  1002         sh   44      100  female\n",
       "2013-01-04  1003  guangzhou   54      110    male\n",
       "2013-01-05  1004   shenzhen   32      110  female\n",
       "2013-01-06  1005   shanghai   34      210    male"
      ]
     },
     "execution_count": 196,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 非\n",
    "df_inner.loc[(df_inner['city'] != 'beijing'), ['id','city','age','category','gender']].sort_values(['id']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 202,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 计数 .count()\n",
    "df_inner.loc[(df_inner['city'] != 'beijing'), ['id','city','age','category','gender']].city.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>1001</td>\n",
       "      <td>beijing</td>\n",
       "      <td>100-A</td>\n",
       "      <td>23</td>\n",
       "      <td>1200</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>10</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>1005</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>1006</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id      city category-size  age  price  gender pay  m-point  \\\n",
       "date                                                                        \n",
       "2013-01-02  1001   beijing         100-A   23   1200    male   Y       10   \n",
       "2013-01-06  1005  shanghai         210-A   34   3299    male   N       40   \n",
       "2013-01-07  1006   beijing         130-F   32   4432  female   Y       40   \n",
       "\n",
       "           group  sign category size  \n",
       "date                                  \n",
       "2013-01-02   low   NaN      100    A  \n",
       "2013-01-06  high   NaN      210    A  \n",
       "2013-01-07  high   1.0      130    F  "
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 使用query函数\n",
    "df_inner.query('city == [\"beijing\", \"shanghai\"]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8931"
      ]
     },
     "execution_count": 204,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 求和 .sum()\n",
    "df_inner.query('city == [\"beijing\", \"shanghai\"]').price.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. 数据汇总"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>beijing</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>guangzhou</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sh</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shanghai</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shenzhen</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id  category-size  age  price  gender  pay  m-point  group  sign  \\\n",
       "city                                                                          \n",
       "beijing     2              2    2      2       2    2        2      2     1   \n",
       "guangzhou   1              1    1      1       1    1        1      1     0   \n",
       "sh          1              1    1      1       1    1        1      1     0   \n",
       "shanghai    1              1    1      1       1    1        1      1     0   \n",
       "shenzhen    1              1    1      1       1    1        1      1     0   \n",
       "\n",
       "           category  size  \n",
       "city                       \n",
       "beijing           2     2  \n",
       "guangzhou         1     1  \n",
       "sh                1     1  \n",
       "shanghai          1     1  \n",
       "shenzhen          1     1  "
      ]
     },
     "execution_count": 211,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.groupby('city').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "city\n",
       "beijing      2\n",
       "guangzhou    1\n",
       "sh           1\n",
       "shanghai     1\n",
       "shenzhen     1\n",
       "Name: id, dtype: int64"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.groupby('city')['id'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "city       size\n",
       "beijing    A       1\n",
       "           F       1\n",
       "guangzhou  A       1\n",
       "sh         B       1\n",
       "shanghai   A       1\n",
       "shenzhen   C       1\n",
       "Name: id, dtype: int64"
      ]
     },
     "execution_count": 213,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.groupby(['city','size'])['id'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>len</th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>beijing</th>\n",
       "      <td>2</td>\n",
       "      <td>5632</td>\n",
       "      <td>2816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>guangzhou</th>\n",
       "      <td>1</td>\n",
       "      <td>2133</td>\n",
       "      <td>2133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sh</th>\n",
       "      <td>1</td>\n",
       "      <td>3299</td>\n",
       "      <td>3299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shanghai</th>\n",
       "      <td>1</td>\n",
       "      <td>3299</td>\n",
       "      <td>3299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>shenzhen</th>\n",
       "      <td>1</td>\n",
       "      <td>5433</td>\n",
       "      <td>5433</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           len   sum  mean\n",
       "city                      \n",
       "beijing      2  5632  2816\n",
       "guangzhou    1  2133  2133\n",
       "sh           1  3299  3299\n",
       "shanghai     1  3299  3299\n",
       "shenzhen     1  5433  5433"
      ]
     },
     "execution_count": 219,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对city字段进行汇总，并分别计算prince的合计和均值\n",
    "df_inner.groupby('city')['price'].agg([len,np.sum, np.mean])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. 数据统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>1004</td>\n",
       "      <td>shenzhen</td>\n",
       "      <td>110-C</td>\n",
       "      <td>32</td>\n",
       "      <td>5433</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>1006</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city category-size  age  price  gender pay  m-point  \\\n",
       "date                                                                         \n",
       "2013-01-04  1003  guangzhou         110-A   54   2133    male   Y       20   \n",
       "2013-01-05  1004   shenzhen         110-C   32   5433  female   Y       40   \n",
       "2013-01-07  1006    beijing         130-F   32   4432  female   Y       40   \n",
       "\n",
       "           group  sign category size  \n",
       "date                                  \n",
       "2013-01-04   low   NaN      110    A  \n",
       "2013-01-05  high   NaN      110    C  \n",
       "2013-01-07  high   1.0      130    F  "
      ]
     },
     "execution_count": 224,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_inner.sample(n=3)  # 随机采样"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>1005</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-07</th>\n",
       "      <td>1006</td>\n",
       "      <td>beijing</td>\n",
       "      <td>130-F</td>\n",
       "      <td>32</td>\n",
       "      <td>4432</td>\n",
       "      <td>female</td>\n",
       "      <td>Y</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>1.0</td>\n",
       "      <td>130</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id      city category-size  age  price  gender pay  m-point  \\\n",
       "date                                                                        \n",
       "2013-01-06  1005  shanghai         210-A   34   3299    male   N       40   \n",
       "2013-01-07  1006   beijing         130-F   32   4432  female   Y       40   \n",
       "\n",
       "           group  sign category size  \n",
       "date                                  \n",
       "2013-01-06  high   NaN      210    A  \n",
       "2013-01-07  high   1.0      130    F  "
      ]
     },
     "execution_count": 225,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 自定义采样权重\n",
    "weights = [0, 0, 0, 0, 0.5, 0.5]\n",
    "df_inner.sample(n=2, weights=weights)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>city</th>\n",
       "      <th>category-size</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>gender</th>\n",
       "      <th>pay</th>\n",
       "      <th>m-point</th>\n",
       "      <th>group</th>\n",
       "      <th>sign</th>\n",
       "      <th>category</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>1005</td>\n",
       "      <td>shanghai</td>\n",
       "      <td>210-A</td>\n",
       "      <td>34</td>\n",
       "      <td>3299</td>\n",
       "      <td>male</td>\n",
       "      <td>N</td>\n",
       "      <td>40</td>\n",
       "      <td>high</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>1003</td>\n",
       "      <td>guangzhou</td>\n",
       "      <td>110-A</td>\n",
       "      <td>54</td>\n",
       "      <td>2133</td>\n",
       "      <td>male</td>\n",
       "      <td>Y</td>\n",
       "      <td>20</td>\n",
       "      <td>low</td>\n",
       "      <td>NaN</td>\n",
       "      <td>110</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              id       city category-size  age  price gender pay  m-point  \\\n",
       "date                                                                        \n",
       "2013-01-06  1005   shanghai         210-A   34   3299   male   N       40   \n",
       "2013-01-04  1003  guangzhou         110-A   54   2133   male   Y       20   \n",
       "\n",
       "           group  sign category size  \n",
       "date                                  \n",
       "2013-01-06  high   NaN      210    A  \n",
       "2013-01-04   low   NaN      110    A  "
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 不放回采样\n",
    "df_inner.sample(n=2, replace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>6.0</td>\n",
       "      <td>1003.50</td>\n",
       "      <td>1.87</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>1002.25</td>\n",
       "      <td>1003.5</td>\n",
       "      <td>1004.75</td>\n",
       "      <td>1006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>6.0</td>\n",
       "      <td>36.50</td>\n",
       "      <td>10.88</td>\n",
       "      <td>23.0</td>\n",
       "      <td>32.00</td>\n",
       "      <td>33.0</td>\n",
       "      <td>41.50</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>price</th>\n",
       "      <td>6.0</td>\n",
       "      <td>3299.33</td>\n",
       "      <td>1523.35</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>2424.50</td>\n",
       "      <td>3299.0</td>\n",
       "      <td>4148.75</td>\n",
       "      <td>5433.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>m-point</th>\n",
       "      <td>6.0</td>\n",
       "      <td>27.00</td>\n",
       "      <td>14.63</td>\n",
       "      <td>10.0</td>\n",
       "      <td>14.00</td>\n",
       "      <td>30.0</td>\n",
       "      <td>40.00</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sign</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         count     mean      std     min      25%     50%      75%     max\n",
       "id         6.0  1003.50     1.87  1001.0  1002.25  1003.5  1004.75  1006.0\n",
       "age        6.0    36.50    10.88    23.0    32.00    33.0    41.50    54.0\n",
       "price      6.0  3299.33  1523.35  1200.0  2424.50  3299.0  4148.75  5433.0\n",
       "m-point    6.0    27.00    14.63    10.0    14.00    30.0    40.00    40.0\n",
       "sign       1.0     1.00      NaN     1.0     1.00     1.0     1.00     1.0"
      ]
     },
     "execution_count": 246,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 统计\n",
    "df_inner.describe().round(2).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1523.3516556155596"
      ]
     },
     "execution_count": 247,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 标准差\n",
    "df_inner['price'].std()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17263.200000000004"
      ]
     },
     "execution_count": 248,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 协方差\n",
    "df_inner['price'].cov(df_inner['m-point'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>m-point</th>\n",
       "      <th>sign</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>3.5</td>\n",
       "      <td>-0.7</td>\n",
       "      <td>1.946000e+03</td>\n",
       "      <td>25.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>-0.7</td>\n",
       "      <td>118.3</td>\n",
       "      <td>-1.354000e+03</td>\n",
       "      <td>-31.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>price</th>\n",
       "      <td>1946.0</td>\n",
       "      <td>-1354.0</td>\n",
       "      <td>2.320600e+06</td>\n",
       "      <td>17263.2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>m-point</th>\n",
       "      <td>25.4</td>\n",
       "      <td>-31.0</td>\n",
       "      <td>1.726320e+04</td>\n",
       "      <td>214.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sign</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id     age         price  m-point  sign\n",
       "id          3.5    -0.7  1.946000e+03     25.4   NaN\n",
       "age        -0.7   118.3 -1.354000e+03    -31.0   NaN\n",
       "price    1946.0 -1354.0  2.320600e+06  17263.2   NaN\n",
       "m-point    25.4   -31.0  1.726320e+04    214.0   NaN\n",
       "sign        NaN     NaN           NaN      NaN   NaN"
      ]
     },
     "execution_count": 249,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 所有数据列间的协方差\n",
    "df_inner.cov()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7746655561708526"
      ]
     },
     "execution_count": 250,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 相关性\n",
    "df_inner['price'].corr(df_inner['m-point'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>age</th>\n",
       "      <th>price</th>\n",
       "      <th>m-point</th>\n",
       "      <th>sign</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.034401</td>\n",
       "      <td>0.682824</td>\n",
       "      <td>0.928096</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>-0.034401</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.081720</td>\n",
       "      <td>-0.194833</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>price</th>\n",
       "      <td>0.682824</td>\n",
       "      <td>-0.081720</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.774666</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>m-point</th>\n",
       "      <td>0.928096</td>\n",
       "      <td>-0.194833</td>\n",
       "      <td>0.774666</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sign</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id       age     price   m-point  sign\n",
       "id       1.000000 -0.034401  0.682824  0.928096   NaN\n",
       "age     -0.034401  1.000000 -0.081720 -0.194833   NaN\n",
       "price    0.682824 -0.081720  1.000000  0.774666   NaN\n",
       "m-point  0.928096 -0.194833  0.774666  1.000000   NaN\n",
       "sign          NaN       NaN       NaN       NaN   NaN"
      ]
     },
     "execution_count": 251,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 所有数据列之间的相关性\n",
    "df_inner.corr()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. 输出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Excel\n",
    "df_inner.to_excel('name.xlsx', sheet_name='sheet_name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [],
   "source": [
    "# csv\n",
    "df_inner.to_csv('name.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
